IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[cms_sp_SiteSelectForUser]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[cms_sp_SiteSelectForUser]
GO
create procedure [dbo].[cms_sp_SiteSelectForUser]
@UserId int,
@AdministrationAccessUserGroupName varchar(250)
as
select distinct
	[Sites].[SiteId] as Id,
	[Sites].[SiteGroupId],
	[Sites].[SiteName],
	[Sites].[CreatedBy],
	[Sites].[DateCreated],
	[Sites].[LastUpdatedBy],
	[Sites].[DateLastUpdated]
from [dbo].[Sites]
  inner join [dbo].[UserGroups] 
    on ([Sites].SiteId = [UserGroups].SiteId and [Sites].SiteGroupId = [UserGroups].SiteGroupId)
	or ([UserGroups].SiteId is null and [UserGroups].SiteGroupId = [Sites].SiteGroupId)
	or ([UserGroups].SiteId is null and [UserGroups].SiteGroupId is null)
  inner join [dbo].[UserGroupLinks]
    on [UserGroupLinks].UserGroupId = [UserGroups].UserGroupId
where 
  [UserGroupLinks].UserId = @UserId and [UserGroups].GroupName = @AdministrationAccessUserGroupName

GO

